MySQL Replace函数

MySQL provides you with a useful string function called REPLACE to allow you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace text, which affects multiple records such as obsolete URL, spelling mistake…

The syntax of REPLACE function is as follows:

UPDATE tbl_name SET field_name = REPLACE(field_name,string_to_find,string_to_replace) WHERE conditions

Note that when searching for text to replace, MySQL uses case-sensitive match to perform search for string to be replaced.

For example, if you want to correct the spelling mistake in the products table in the sample database, you use the REPLACE function as follows:

UPDATE products SET productDescription = REPLACE(productDescription,'abuot','about')

The query will look at the column productDescription of the table products and find for all occurrences of spelling mistake ‘abuot’ and replace it by the correct word ‘about’.

It is very important to note that in the REPLACE function, the first parameter is the field name without quotes. If you put the quotes to the field name like ‘field_name’, the query will update the content of that column to ‘field_name’, which is causing unexpected data loss.

Currently REPLACE function does not support regular expression so if you need to replace a text string by a pattern you need to use MySQL user defined function (UDF) from external library, check it out here MySQL UDF with Regex

原文链接：http://outofmemory.cn/mysql/function/mysql-string-replace-function